package jnpf.database.sql.append.create;

import jnpf.database.enums.DbAliasEnum;
import jnpf.database.enums.datatype.DataTypeEnum;
import jnpf.database.model.DataTypeModel;
import jnpf.database.model.DbTableFieldModel;
import jnpf.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.regex.Pattern;

/**
 * 添表SQL模板
 *
 * @author JNPF开发平台组
 * @version V3.2.0
 * @copyright 引迈信息技术有限公司
 * @date 2021/10/6
 */
@Slf4j
public class CreateSql {


    /**
     * 获取 - SQL
     */
    public static String getCreTabSql(CreateSqlDTO createSqlDTO) throws Exception{
        String fields = getFields(createSqlDTO);
        String comments = getComments(createSqlDTO);
        /* ==================框架======================
         * 框架 - 基础
         * 基本框架：CREATE TABLE + {表名} + ({字段集合}) + {注释}
         * @return SQL
         */
        return "CREATE TABLE\t" + createSqlDTO.getNewTable() + fields + comments;
    }

    /*============================ 字段 ================================*/

    /**
     * 装配 - 字段集合
     */
    private static String getFields(CreateSqlDTO createSqlDTO) throws Exception{
        String fieldSql = "";
        //新增表，初始字段不能为空
        if(CollectionUtils.isNotEmpty(createSqlDTO.getFields())){
            for(DbTableFieldModel item : createSqlDTO.getFields()){
                //类型转换 (fieldName即是fieldComment);
                String fieldSqlFragment = fieldsSqlFrame(item, createSqlDTO);
                fieldSql = fieldSql + fieldSqlFragment;
            }
            //去除最后一个逗号
            return " ( " + fieldSql.substring(0,fieldSql.length()-1) + " ) ";
        }else {
            throw new Exception("没有初始字段");
        }
    }

    /**
     * 装配 - 单个字段
     * @param field 字段名
     * @return 单个字段SQL片段
     */
    private static String fieldsSqlFrame(DbTableFieldModel field, CreateSqlDTO createSqlDTO){
        try{
            // 设置
            DataTypeModel dataTypeModel = DataTypeEnum.getDataTypeModel(field.getDataType(), createSqlDTO.getDbBase());
            // 传入一些项目中没有内置的类型
            String dataType;
            if(dataTypeModel != null){
                //MySQL独有,varchar作为主键的特殊处理
                dataTypeModel = createSqlDTO.getMysqlDataTypeModel(field, dataTypeModel);
                dataType = getDataType(field.getDataLength(), dataTypeModel);
            }else {
                dataType = getOriginDataType(field.getDataLength(), field.getDataType());
            }

            //允空
            String notNull = DbAliasEnum.ALLOW_NULL.isFalse().equals(field.getAllowNull()) ? "\tNOT NULL\t" : "";
            //主键
            String primaryKey = DbAliasEnum.PRIMARY_KEY.isTrue().equals(field.getPrimaryKey()) ? "\tPRIMARY KEY\t" : "";
            //MySQL独有的字段注释方式
            String mysqlFieldComment = createSqlDTO.getMysqlFieldComment(field.getFieldName());
            /* ===============字段字段===================
             * 字段：{字段名} + {字段类型} + ({字段长度} + {非空限定} + {主键判断} + [{mysql注释}],
             * @return 单个字段SQL
             */
            // TODO 这个地方会出现关键字问题比如field为name
            return field.getField() + " " + dataType + " " + notNull + " " + primaryKey + " " + mysqlFieldComment + ",";
        }catch (Exception e){
            e.getMessage();
        }
        return "";
    }

    /**
     * 设置 - 类型
     */
    private static String getDataType(String dataLength, DataTypeModel dataTypeModel){
        // 长度设置
        if(dataTypeModel.getLengthModifyFlag()){
            String precision = getPrecision(dataTypeModel, dataLength);
            return dataTypeModel.getDbFieldType() +
                    "("+ dataTypeModel.getCurrentLength() + precision + ")";
        }else {
            return dataTypeModel.getDbFieldType();
        }
    }

    private static String getOriginDataType(String dataLength, String dataType){
        if(numFlag(dataLength) && Integer.parseInt(dataLength) > 0){
            return dataType + "("+ dataLength + ")";
        }
        return dataType;
    }

    private static String getPrecision(DataTypeModel dataTypeModel, String dataLength){
        //精度处理(","英文逗号作为精度标识)
        if(dataLength.contains(",")){
            String[] split = dataLength.split(",");
            dataLength = split[0];
            dataTypeModel.setCurrentLength(getLengthInfo(dataTypeModel, dataLength));
            String precision = split[1];
            Integer precisionMax = dataTypeModel.getPrecisionMax();
            Integer defaultPrecision = dataTypeModel.getDefaultPrecision();
            //存在precisionMax说明此类型可以进行精度设置
            if(precisionMax != null){
                //精度为数字
                if(numFlag(precision) || Integer.parseInt(precision) > 0 ||
                        Integer.parseInt(precision) < precisionMax){
                    return "," + precision;
                }
                return "," + defaultPrecision;
            }
        }else {
            dataTypeModel.setCurrentLength(getLengthInfo(dataTypeModel, dataLength));
        }
        return "";
    }

    private static Integer getLengthInfo(DataTypeModel dataTypeModel, String dataLength){
        //1、长度为空 || 字符串不是整型（无效长度）
        if(StringUtils.isEmpty(dataLength) || !numFlag(dataLength)){
            //返回默认值
            return dataTypeModel.getDefaultLength();
        }else {
            int lengthNum = Integer.parseInt(dataLength);
            //2、长度小于1 || 长度大于最大限制
            if(lengthNum < 1 || lengthNum > dataTypeModel.getLengthMax()){
                return dataTypeModel.getDefaultLength();
            }else {
                return lengthNum;
            }
        }
    }

    private static Boolean numFlag(String num){
        return Pattern.compile("^[-\\+]?[\\d]*$").matcher(num).matches();
    }

    /*============================ 注释 ================================*/

    private static String getComments(CreateSqlDTO createSqlDTO){

        if(StringUtil.isNotEmpty(createSqlDTO.getTableComment())){
            // MySQL、SQLServer独有注释方法
            String comments = createSqlDTO.getMysqlSqlServerComments();
            if(StringUtils.isNotEmpty(comments)){
                return comments;
            }else {
                return setComments(createSqlDTO);
            }
        }
        return "";
    }

    /**
     * 装配 - 注释、表名
     */
    private static String setComments(CreateSqlDTO createSqlDTO){
        // 设置表注释
        String tableComment = "TABLE\t" + createSqlDTO.getNewTable();
        tableComment = getComment(tableComment, createSqlDTO.getTableComment());

        // 设置字段注释
        String fieldComments = "";
        for(DbTableFieldModel field : createSqlDTO.getFields()){
            String fieldComment = "\tCOLUMN\t" + createSqlDTO.getNewTable() + "." + field.getField();
            fieldComments = fieldComments + getComment(fieldComment, field.getFieldName());
        }
        return ";\t" + tableComment + fieldComments;
    }

    private static String getComment(String whereComment,String comment){
        return "COMMENT ON\t" + whereComment + "\tis\t" + "\'" + comment +"\';";
    }

}
